﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using MediaAssistant.DAL.Constants;
using MediaAssistant.DAL.Helper;
using Newtonsoft.Json.Linq;
using Tags.ASF;
using Tags.ID3;

namespace MediaAssistant.DAL
{
    public partial class MediaAssistantSqliteDbEntities
    {
        public static bool IsUpdatedVersion { get; private set; }
        public const string Unknown = "Unknown";

        private static Music CreateMusic(string fileName)
        {
            var fileInfo = new FileInfo(fileName);
            var music = new Music
            {
                Id = Guid.NewGuid().ToString(),
                FileName = fileInfo.Name,
                FullPath = fileName,
                Size = fileInfo.Length,
                SizeText = Utility.GetFileSize(fileInfo.Length),
                Score = 0,
                Status = MusicStatus.Pending,
                TagStatus = TagStatus.InvalidData
            };
            return music;
        }

        private static void UpdateTags(Music music)
        {
            try
            {
                var filePath = music.FullPath;
                var extension = Path.GetExtension(filePath);
                if (extension == null)
                    return;
                switch (extension.ToLower())
                {
                    case ".mp3":
                        ReadMp3FileTags(music, filePath);
                        break;
                    case ".wma":
                        ReadWmaFileTags(music, filePath);
                        break;
                }
                music.TagStatus = TagStatus.Synced;
            }
            catch (Exception)
            {
                //ID3 tag not found
            }
        }

        private static void ReadWmaFileTags(Music music, string filePath)
        {
            //WMA File: Title:Title;Album:WM/AlbumTitle;Artist:WM/AlbumArtist;Mood:WM/Mood;Genre:WM/Genre;Initial Key:WM/InitialKey;Language:WM/Language;Category:WM/Category;Conductor:WM/Conductor;Description:Description;Track Number:WM/TrackNumber;Part Of Set:WM/PartOfSet;Playlist Delay:WM/PlaylistDelay;Sub Title:WM/SubTitle;Sub Title description:WM/SubTitleDescription;Rating:Rating;Author:Author;Writer:WM/Writer;Director:WM/Director;Original Album Title:WM/OriginalAlbumTitle;Original Artist:WM/OriginalArtist;Original FileName:WM/OriginalFilename;Original Lyricist:WM/OriginalLyricist;Original Release Time:WM/OriginalReleaseTime;Original Release Year:WM/OriginalReleaseYear;Publisher:WM/Publisher;Producer:WM/Producer;Modified By:WM/ModifiedBy;Encoded By:WM/EncodedBy;ISRC:WM/ISRC;Copyright:Copyright;Radio Station Name:WM/RadioStationName;Radio Station Owner:WM/RadioStationOwner;Tool Name:WM/ToolName;Tool Version:WM/ToolVersion;FileName:;Path:
            var wmaFile = new ASFTagInfo(filePath, true);
            if (String.IsNullOrWhiteSpace(wmaFile.ContentDescription.Title) == false)
                music.Title = wmaFile.ContentDescription.Title;
            else
                music.Title = wmaFile.ExContentDescription["Title"] as string;
            music.Album = wmaFile.ExContentDescription["WM/AlbumTitle"] as string;
            music.Genre = wmaFile.ExContentDescription["WM/Genre"] as string;
            music.Artist = wmaFile.ExContentDescription["WM/AlbumArtist"] as string;
        }

        private static void ReadMp3FileTags(Music music, string filePath)
        {
            var id3 = new ID3Info(filePath, true);
            if (id3.ID3v2Info.HaveTag)
            {
                //MP3 File: Album:TALB;BPM ( Beats Per Minutes):TBPM;Composer:TCOM;Genre:TCON;Copyright Message:TCOP;Date:TDAT;Encoding Time:TDEN;Playlist Delay:TDLY;Orginal Release Time:TDOR;Recording Time:TDRC;Release Time:TDRL;Tagging Time:TDTG;Encoded By:TENC;Lyric/Text Writer:TEXT;File Type:TFLT;Time:TIME;Involved People List:TIPL;Content Group Description:TIT1;Title:TIT2;Subtitle/Desripction:TIT3;Initial Key:TKEY;Language:TLAN;Length:TLEN;Musician Credits List:TMCL;Media Type:TMED;Mood:TMOO;Orginal Title:TOAL;Orginal Filename:TOFN;Orginal Lyricist:TOLY;Orginal Artist:TOPE;Orginal Release Year:TORY;File Owner:TOWN;Lead Artist:TPE1;Band Artist:TPE2;Conductor:TPE3;Interpreted:TPE4;Part of set:TPOS;Produced Notice:TPRO;Publisher:TPUB;Track Number:TRCK;Recording Date:TRDA;Internet Radio Station Name:TRSN;Internet Radio Station Owner:TRSO;Size:TSIZ;Album Sort Order:TSOA;Preformer Sort Order:TSOP;Title Sort Order:TSOT;ISRC:TSRC;Software/Hardware Used For Encoding:TSSE;Set Subtitle:TSST;Year:TYER;File Name:;Tag Size:;ID3v1:;ID3v2:;Path:
                music.Album = id3.ID3v2Info.GetTextFrame("TALB");
                music.Artist = id3.ID3v2Info.GetTextFrame("TPE1");
                music.Genre = id3.ID3v2Info.GetTextFrame("TCON");
                music.Composer = id3.ID3v2Info.GetTextFrame("TCOM");
                music.Title = id3.ID3v2Info.GetTextFrame("TIT2");
                music.Time = id3.ID3v2Info.Length;
                music.TimeText = ConvertTo.String( new TimeSpan(0, 0, 0, 0, id3.ID3v2Info.Length));
            }
            else if (id3.ID3v1Info.HaveTag)
            {
                music.Album = id3.ID3v1Info.Album;
                music.Artist = id3.ID3v1Info.Artist;
                music.Title = id3.ID3v1Info.Title;
            }
        }
        public void UpdateMusic(Music music)
        {
            UnlinkMusic(music);
            LinkMusic(music);
            music.TagStatus = TagStatus.InvalidFile;
        }

        public void UpdateFileTag(Music music)
        {
            if (File.Exists(music.FullPath))
            {
                try
                {
                    var extension = Path.GetExtension(music.FileName);
                    if (extension == null)
                        return;
                    if (extension.ToLower() == ".mp3")
                    {
                        UpdateMp3FileTag(music);
                    }
                    else if (extension.ToLower() == ".wma")
                    {
                        UpdateWmaFileTag(music);
                    }
                    music.TagStatus = TagStatus.Synced;
                }
                catch (Exception)
                {
                }
                
            }
        }

        private static void UpdateWmaFileTag(Music music)
        {
            var wmaFile = new ASFTagInfo(music.FullPath, true);
            if (String.IsNullOrWhiteSpace(wmaFile.ContentDescription.Title) == false)
                wmaFile.ContentDescription.Title = music.Title ?? string.Empty;
            else
                wmaFile.ExContentDescription["Title"] = music.Title ?? string.Empty;
            wmaFile.ExContentDescription["WM/AlbumTitle"] = music.Album ?? string.Empty;
            wmaFile.ExContentDescription["WM/Genre"] = music.Genre ?? string.Empty;
            wmaFile.ExContentDescription["WM/AlbumArtist"] = music.Artist ?? string.Empty;
            wmaFile.Save();
        }

        private static void UpdateMp3FileTag(Music music)
        {
            var fileInfo = new FileInfo(music.FullPath);
            if(fileInfo.IsReadOnly)
            {
                File.SetAttributes(music.FullPath,FileAttributes.Normal);
            }
            var id3 = new ID3Info(music.FullPath, true);
            id3.ID3v2Info.SetTextFrame("TALB", music.Album ?? string.Empty);
            id3.ID3v2Info.SetTextFrame("TPE1", music.Artist ?? string.Empty);
            id3.ID3v2Info.SetTextFrame("TCON", music.Genre ?? string.Empty);
            id3.ID3v2Info.SetTextFrame("TCOM", music.Composer ?? string.Empty);
            id3.ID3v2Info.SetTextFrame("TIT2", music.Title ?? string.Empty);
            id3.ID3v2Info.Save();
        }

        public void CreateFixedLibraryItems()
        {
            var index = 1;
            CreateLibraryItem(LibraryItemType.MovieLibrary, LibraryItemType.MovieLibrary, "Movies", index++);
            CreateLibraryItem(LibraryItemType.MovieGenreLibrary, LibraryItemType.MovieGenreLibrary, "Genres", index++);
            CreateLibraryItem(LibraryItemType.ActorLibrary, LibraryItemType.ActorLibrary, "Stars", index++);
            CreateLibraryItem(LibraryItemType.DirectorLibrary, LibraryItemType.DirectorLibrary, "Directors", index++);
            CreateLibraryItem(LibraryItemType.WriterLibrary, LibraryItemType.WriterLibrary, "Writers", index++);
            CreateLibraryItem(LibraryItemType.YearLibrary, LibraryItemType.YearLibrary, "Years", index++);
            CreateLibraryItem(LibraryItemType.RatedLibrary, LibraryItemType.RatedLibrary, "Rated", index++);

            CreateLibraryItemForType(LibraryItemType.UnreadMovieLibrary, LibraryItemType.UnreadMovieLibrary, "New", index++);
            CreateLibraryItemForType(LibraryItemType.StaredMovieLibrary, LibraryItemType.StaredMovieLibrary, "Favorit", index++);
            CreateLibraryItemForType(LibraryItemType.RecommendedMovieLibrary, LibraryItemType.RecommendedMovieLibrary, "Recommended", index++);
            CreateLibraryItemForType(LibraryItemType.WatchListLibrary, LibraryItemType.WatchListLibrary, "Watched", index++);
            CreateLibraryItemForType(LibraryItemType.WishListLibrary, LibraryItemType.WishListLibrary, "Wish List", index++);
            CreateLibraryItemForType(LibraryItemType.IMDbMustWatchList, LibraryItemType.IMDbMustWatchList, "Must Watch", index++);
            CreateLibraryItemForType(LibraryItemType.RecentlyAddedMovieLibrary, LibraryItemType.RecentlyAddedMovieLibrary, "Recently Added", index++);
            CreateLibraryItemForType(LibraryItemType.RecentlyPlayedMovieLibrary, LibraryItemType.RecentlyPlayedMovieLibrary, "Recently Played", index++);
            CreateLibraryItemForType(LibraryItemType.ProcessingLibrary, LibraryItemType.ProcessingLibrary, "Processing", index++);
            CreateLibraryItemForType(LibraryItemType.FailedLibrary, LibraryItemType.FailedLibrary, "Failed", index++);

            CreateLibraryItem(LibraryItemType.MusicLibrary, LibraryItemType.MusicLibrary, "Musics", index++);
            CreateLibraryItem(LibraryItemType.GenreLibrary, LibraryItemType.GenreLibrary, "Genres", index++);
            CreateLibraryItem(LibraryItemType.AlbumLibrary, LibraryItemType.AlbumLibrary, "Albums", index++);
            CreateLibraryItem(LibraryItemType.ArtistLibrary, LibraryItemType.ArtistLibrary, "Artists", index++);
            CreateLibraryItem(LibraryItemType.ComposerLibrary, LibraryItemType.ComposerLibrary, "Composers", index++);

            CreateLibraryItem(LibraryItemType.PlayListLibrary, LibraryItemType.PlayListLibrary, "Playlists", index++);
            CreateLibraryItem(LibraryItemType.NowPlayingPlaylist, LibraryItemType.Playlist, LibraryItemType.NowPlayingPlaylist, index++);
            CreateLibraryItem(LibraryItemType.LastImportedPlaylist, LibraryItemType.Playlist, LibraryItemType.LastImportedPlaylist, index);
        }
        public Movie UpdateMovie(Movie movie, JObject jsonObject, bool saveAfterUpdate)
        {
          /*{
  "Title": "The Godfather: Part II",
  "Year": "1974",
  "Rated": "R",
  "Released": "20 Dec 1974",
  "Runtime": "3 h 20 min",
  "Genre": "Crime, Drama",
  "Director": "Francis Ford Coppola",
  "Writer": "Francis Ford Coppola, Mario Puzo",
  "Actors": "Al Pacino, Robert De Niro, Robert Duvall, Diane Keaton",
  "Plot": "The early life and career of Vito Corleone in 1920s New York is portrayed while his son, Michael, expands and tightens his grip on his crime syndicate stretching from Lake Tahoe, Nevada to pre-revolution 1958 Cuba.",
  "Poster": "http://ia.media-imdb.com/images/M/MV5BMTY2ODA1NDE4OV5BMl5BanBnXkFtZTcwNTQzNDM5MQ@@._V1_SX640.jpg",
  "imdbRating": "9.0",
  "imdbVotes": "364,844",
  "imdbID": "tt0071562",
  "Response": "True"
}*/
            var imdbId = (string)jsonObject.SelectToken("imdbID");
            var exisingMovie = Movies.FirstOrDefault(m => m.IMDBId == imdbId);    
            var resultMovie = movie;
            if (exisingMovie != null)
            {
                exisingMovie.UpdateMovie(jsonObject);
                UnLinkMovie(exisingMovie);
                LinkMovie(exisingMovie,jsonObject);
                if (string.IsNullOrWhiteSpace(movie.FullPath) == false)
                {
                    UpdateExistingMovie(exisingMovie, movie);
                }
                if(movie!=exisingMovie)
                    DeleteMovie(movie);
                exisingMovie.Status = MovieStatus.Done;
                resultMovie = exisingMovie;
            }
            else
            {
                UpdateNewMovie(jsonObject, imdbId, movie);
            }
            if(saveAfterUpdate)
                Save();
            return resultMovie;
        }

        private bool _turnOffSaving;
        public bool TurnOffSaving
        {
            get
            {
                return _turnOffSaving;
            }
            set
            {
                _turnOffSaving = value;
                if (!value)
                    Save();
            }
        }

        public void Save()
        {
            if(!TurnOffSaving)
                SaveChanges();
        }

        private void UpdateNewMovie(JObject jsonObject, string imdbId, Movie movie)
        {
            movie.UpdateMovie(jsonObject);
            movie.IMDBId = imdbId;
            movie.PosterImage = null;
            LinkMovie(movie, jsonObject);
            movie.Status = MovieStatus.Done;
        }

        private void DeleteMovie(Movie movie)
        {
            if (movie.EntityState == EntityState.Detached)
                return;
            UnLinkMovie(movie);
            foreach (var profileMovie in movie.ProfileMovies.ToArray())
            {
                ProfileMovies.DeleteObject(profileMovie);
            }
            foreach (var location in movie.AlternativeLocations.ToArray())
            {
                AlternativeLocations.DeleteObject(location);
            }
            Movies.DeleteObject(movie);
        }

        private void UpdateExistingMovie(Movie exisingMovie, Movie movie)
        {
            var fileInfo = new FileInfo(movie.FullPath);
            if (string.IsNullOrWhiteSpace(exisingMovie.FullPath))
            {
                exisingMovie.FullPath = movie.FullPath;
                exisingMovie.FileName = movie.FileName;
                exisingMovie.Size = fileInfo.Exists ? fileInfo.Length : 0;
                exisingMovie.SizeText = Utility.GetFileSize((long) exisingMovie.Size);
            }
            else if (exisingMovie.AlternativeLocations.Any(l => l.Location == movie.FullPath) == false)
            {
                AddAlternativeLocation(exisingMovie, movie.FullPath);
            }
            exisingMovie.Index = movie.Index;
        }

        private void AddAlternativeLocation(Movie exisingMovie, string fullPath)
        {
            var fileInfo = new FileInfo(fullPath);
            var alternativeLocation = new AlternativeLocation
                                          {
                                              Id = Guid.NewGuid().ToString(),
                                              Movie = exisingMovie,
                                              Location = fullPath,
                                              Size = fileInfo.Exists? fileInfo.Length:0,
                                          };
            AlternativeLocations.AddObject(alternativeLocation);
        }

        public void AddFile(string fileName)
        {
            if (Utility.IsMusicFile(fileName))
            {
                AddMusicFile(fileName);
            }
            else if (Utility.IsMovieFile(fileName))
            {
                AddMovieFile(fileName);
            }
        }

        public void AddMovieFile(string fileName)
        {
            if (IsMovieFullPathAdded(fileName))
                return;

            var movie = GetExistingMovie(fileName);
            if(movie!=null)
            {
                AddAlternativeLocation(movie,fileName);
            }
            else
            {
                movie = CreateMovie(fileName);
                if (movie.Size < Utility.MinMovieFileSize)
                    return;
                Movies.AddObject(movie);    
            }
            Save();
        }

        private Movie GetExistingMovie(string fileName)
        {
            var fileInfo = new FileInfo(fileName);
            return !fileInfo.Exists ? null : Enumerable.FirstOrDefault(Movies, movie => IsSameMovieFile(fileInfo, movie));
        }

        private static bool IsSameMovieFile(FileInfo fileInfo, Movie movie)
        {
            if (IsSameFileNameAndSize(fileInfo, movie.FileName, movie.Size))
                return true;
            if(movie.AlternativeLocations.Any(al=>IsSameFileNameAndSize(fileInfo,Path.GetFileName(al.Location), al.Size)))
            {
                return true;
            }
            return IsSameFileNameAndSize(fileInfo, movie.FileName, movie.Size);
        }

        private static bool IsSameFileNameAndSize(FileInfo fileInfo, string fileName, decimal? size)
        {
            if (size==null)
                return false;
            return String.Compare(fileName, fileInfo.Name, StringComparison.OrdinalIgnoreCase) == 0 && size == fileInfo.Length;
        }

        public bool IsMovieFullPathAdded(string fileName)
        {
            return Movies.Any(o => String.Compare(o.FullPath, fileName, StringComparison.InvariantCultureIgnoreCase) == 0);
        }

        public IEnumerable<Movie> UpdateTitles(bool showDrivesMoviesOnly, Profile selectedProfile)
        {
            var processingMovies=UpdateProcessingTitle(showDrivesMoviesOnly);

            UpdateFailedTitle(showDrivesMoviesOnly);

            UpdateNewMovieTitle(showDrivesMoviesOnly, selectedProfile);

            UpdateFevoriteMovieTitle(showDrivesMoviesOnly, selectedProfile);

            UpdateWatchedMovieTitle(showDrivesMoviesOnly, selectedProfile);

            UpdateWishListTitle(showDrivesMoviesOnly, selectedProfile);

            UpdateRecommendedMovieTitle(showDrivesMoviesOnly, selectedProfile);
            var libraryItem = GetOrCreateFixedLibraryItem(LibraryItemType.IMDbMustWatchList);
            libraryItem.Title = string.Format("Must Watch ({0})", libraryItem.Movies.Where(m=>m.CanShow(showDrivesMoviesOnly)).Count());
            return processingMovies;
        }

        public void UpdateRecommendedMovieTitle(bool showDrivesMoviesOnly, Profile selectedProfile)
        {
            var libraryItem = GetOrCreateFixedLibraryItem(LibraryItemType.RecommendedMovieLibrary);
            libraryItem.Title = string.Format("Recommended ({0})", GetRecommendedMovies(selectedProfile).Where(m => m.CanShow(showDrivesMoviesOnly)).Count());
        }

        public void UpdateWishListTitle(bool showDrivesMoviesOnly, Profile selectedProfile)
        {
            var libraryItem = GetOrCreateFixedLibraryItem(LibraryItemType.WishListLibrary);
            libraryItem.Title = string.Format("Wish List ({0})", GetWishListMovie(selectedProfile).Where(m => m.CanShow(showDrivesMoviesOnly)).Count());
        }

        public void UpdateWatchedMovieTitle(bool showDrivesMoviesOnly, Profile selectedProfile)
        {
            var libraryItem = GetOrCreateFixedLibraryItem(LibraryItemType.WatchListLibrary);
            libraryItem.Title = string.Format("Watched ({0})", GetWatchedMovies(selectedProfile).Where(m => m.CanShow(showDrivesMoviesOnly)).Count());
        }

        public void UpdateFevoriteMovieTitle(bool showDrivesMoviesOnly, Profile selectedProfile)
        {
            var libraryItem = GetOrCreateFixedLibraryItem(LibraryItemType.StaredMovieLibrary);
            libraryItem.Title = string.Format("Favorite ({0})", GetStarMovies(selectedProfile).Where(m => m.CanShow(showDrivesMoviesOnly)).Count());
        }

        public void UpdateNewMovieTitle(bool showDrivesMoviesOnly, Profile selectedProfile)
        {
            var libraryItem = GetOrCreateFixedLibraryItem(LibraryItemType.UnreadMovieLibrary);
            libraryItem.Title = string.Format("New ({0})", GetNewMovies(selectedProfile).Where(m => m.CanShow(showDrivesMoviesOnly)).Count());
        }

        public void UpdateFailedTitle(bool showDrivesMoviesOnly)
        {
            var libraryItem = GetOrCreateFixedLibraryItem(LibraryItemType.FailedLibrary);
            libraryItem.Title = string.Format("Failed ({0})", GetFailedMovies().Where(m => m.CanShow(showDrivesMoviesOnly)).Count());
        }

        public IEnumerable<Movie> UpdateProcessingTitle(bool showDrivesMoviesOnly)
        {
            var libraryItem = GetOrCreateFixedLibraryItem(LibraryItemType.ProcessingLibrary);
            var movies = GetProcessingMovies().Where(m=> m.CanShow(showDrivesMoviesOnly));
            libraryItem.Title = string.Format("Processing ({0})", movies.Count());
            return movies;
        }

        public IEnumerable<Movie> GetRecommendedMovies(Profile selectedProfile)
        {
            var profileMovieIds = selectedProfile.ProfileMovies.Where(pm => pm.Watched==false && pm.Recommendation > 0).OrderByDescending(pm=>pm.Recommendation).Select(pm => pm.MovieId).ToList();
            return Movies.Where(m => profileMovieIds.Contains(m.Id)).ToList();
        }
        public IEnumerable<Movie> GetProcessingMovies()
        {
            return Movies.Where(m => m.Status == MovieStatus.Pending || m.Status == MovieStatus.Processing);
        }


        private static Movie CreateMovie(string fileName)
        {
            var fileInfo = new FileInfo(fileName);
            var movie = new Movie
            {
                Id = Guid.NewGuid().ToString(),
                FileName = fileInfo.Name,
                FullPath = fileName,
                Size = fileInfo.Length,
                SizeText = Utility.GetFileSize(fileInfo.Length),
                CreateDate = DateTime.Now,
                Status = MovieStatus.Pending,
                WeightedRating = 0
            };
            return movie;
        }

        public void AddMusicFile(string fileName)
        {
            if (IsMusicFileAdded(fileName))
                return;
            var music = CreateMusic(fileName);
            Musics.AddObject(music);
            AddMusicToLibrary(music, GetOrCreateFixedLibraryItem(LibraryItemType.LastImportedPlaylist));
        }

        public void UpdateTagAndLink(Music music)
        {
            UpdateTags(music);
            UnlinkMusic(music);
            LinkMusic(music);
        }

        public bool IsMusicFileAdded(string fileName)
        {
            return Musics.Any(o => String.Compare(o.FullPath, fileName, StringComparison.OrdinalIgnoreCase) == 0);
        }

        public void LinkMovie(Movie movie, JObject jsonObject)
        {
            UnLinkMovie(movie);
            AddMovieToLibrary(jsonObject, movie, "Genre", LibraryItemType.MovieGenreLibrary, LibraryItemType.MovieGenre);
            AddMovieToLibrary(jsonObject, movie, "Director", LibraryItemType.DirectorLibrary, LibraryItemType.Director);
            AddMovieToLibrary(jsonObject, movie, "Writer", LibraryItemType.WriterLibrary, LibraryItemType.Writer);
            AddMovieToLibrary(jsonObject, movie, "Actors", LibraryItemType.ActorLibrary, LibraryItemType.Actor);
            if (string.IsNullOrWhiteSpace(movie.Rated) == false)
                AddMovieToLibrary(movie.Rated, LibraryItemType.RatedLibrary, LibraryItemType.Rated, movie);
            if (movie.Year.HasValue)
            {
                AddMovieToLibrary(movie.Year.ToString(), LibraryItemType.YearLibrary, LibraryItemType.Year, movie);
            }
            movie.UpdateLibraryInformation();
            Save();
        }

        private static void UnLinkMovie(Movie movie)
        {
            foreach (var libraryItem in movie.LibraryItems.ToArray())
            {
                libraryItem.Movies.Remove(movie);
            }
        }

        private void AddMovieToLibrary(JObject jsonObject, Movie movie, string tokenName, string parentLibraryType, string libraryItemType)
        {
            var tokens = (string)jsonObject.SelectToken(tokenName);
            foreach (var token in tokens.Split(',').Select(s => s.Trim(' ')))
            {
                AddMovieToLibrary(token, parentLibraryType, libraryItemType, movie);
            }
        }

        public void AddMovieToLibrary(string title, string parentLibraryType, string libraryItemType, Movie movie)
        {
            var tokenTitle = GetTitle(title);
            var libraryItem = CreateLibraryItem(GetOrCreateFixedLibraryItem(parentLibraryType), tokenTitle, libraryItemType, null);    
            if (libraryItem.Movies.Contains(movie)) return;
            libraryItem.Movies.Add(movie);
        }

        public void LinkMusic(Music music)
        {
            AddToLibrary(music, music.Genre, LibraryItemType.Genre);
            AddToLibrary(music, music.Artist, LibraryItemType.Artist);
            AddToLibrary(music, music.Album, LibraryItemType.Album);
            AddToLibrary(music, music.Composer, LibraryItemType.Composer);
            Save();
        }

        private void AddToLibrary(Music music, string title, string type)
        {
            var genreTitle = GetTitle(title);
            var genre = CreateLibraryItem(GetOrCreateFixedLibraryItem(LibraryItemType.GetParentType(type)), genreTitle, type, null);
            AddMusicToLibrary(music, genre);
        }

        public void UnlinkMusic(Music music)
        {
            foreach (var libraryItem in music.LibraryItems.Where(o=>o.Type!=LibraryItemType.Playlist).ToArray())
            {
                music.LibraryItems.Remove(libraryItem);
                libraryItem.Musics.Remove(music);
                if(libraryItem.Musics.Count==0)
                {
                    RemoveLibraryItem(libraryItem);
                }
                Save();
            }
        }

        public void RemoveLibraryItem(LibraryItem libraryItem)
        {
            foreach (var music in libraryItem.Musics.ToArray())
            {
                libraryItem.Musics.Remove(music);
            }
            foreach (var movie in libraryItem.Movies.ToArray())
            {
                libraryItem.Movies.Remove(movie);
            }
            foreach (var parent in libraryItem.Parents.ToArray())
            {
                parent.Children.Remove(libraryItem);
            }
            foreach (var child in libraryItem.Children.ToArray())
            {
                child.Parents.Remove(libraryItem);
            }
            LibraryItems.DeleteObject(libraryItem);
        }

        private static string GetTitle(string actualTitle)
        {
            return String.IsNullOrWhiteSpace(actualTitle) ? Unknown : actualTitle.Trim();
        }

        private void AddMusicToLibrary(Music music, LibraryItem album)
        {
            if (album.Musics.Contains(music) == false)
            {
                album.Musics.Add(music);
            }
        }

        private LibraryItem CreateLibraryItem(LibraryItem parent, string title, string childType, string mainLibraryType)
        {
            var child = parent.Children.FirstOrDefault(a =>  string.Compare(a.Title,title,true)==0);
            if (child == null)
            {
                child = new LibraryItem { Id = Guid.NewGuid().ToString(), Title = title, Type = childType, Index = -1, HasChildren = false};
                parent.Children.Add(child);
                parent.HasChildren = true;
                if (mainLibraryType != null)
                {
                    var mainLibrary = GetOrCreateFixedLibraryItem(mainLibraryType);
                    mainLibrary.Children.Add(child);
                    mainLibrary.HasChildren = true;
                }
                LibraryItems.AddObject(child);
                Save();
            }
            return child;
        }

        public LibraryItem CreateLibraryItem(string type, string title)
        {
            return CreateLibraryItem(Guid.NewGuid().ToString(), type, title, -1);
        }
        private LibraryItem CreateLibraryItem(string id, string type, string title, int index)
        {
            var item = LibraryItems.FirstOrDefault(o => o.Type == type && string.Compare(o.Title,title,true)==0);
            if (item == null)
            {
                item = new LibraryItem { Id = id, Title = title, Type = type, Index = index, HasChildren = false};
                if (LibraryItemType.IsRootItem(type)==false)
                {
                    var parent = GetOrCreateFixedLibraryItem(LibraryItemType.GetParentType(type));
                    item.Parents.Add(parent);
                    parent.HasChildren = true;
                }
                LibraryItems.AddObject(item);
                Save();
            }
            return item;
        }
        private void CreateLibraryItemForType(string id, string type, string title, int index)
        {
            var item = LibraryItems.FirstOrDefault(o => o.Type == type);
            if (item == null)
            {
                item = new LibraryItem { Id = id, Title = title, Type = type, Index = index, HasChildren = false };
                if (LibraryItemType.IsRootItem(type)==false)
                {
                    var parent = GetOrCreateFixedLibraryItem(LibraryItemType.GetParentType(type));
                    item.Parents.Add(parent);
                    parent.HasChildren = true;
                }
                LibraryItems.AddObject(item);
                Save();
            }
            else
            {
                item.Index = index;
                Save();
            }
            return;
        }
        public LibraryItem GetOrCreateFixedLibraryItem(string type)
        {
            var item = LibraryItems.FirstOrDefault(o => o.Id == type);
            if (item == null)
            {
                item = new LibraryItem { Id = type, Title = type, Type = type, Index = -1, HasChildren = false };
                LibraryItems.AddObject(item);
                if (LibraryItemType.IsRootItem(type)==false)
                {
                    var parent = GetOrCreateFixedLibraryItem(type == LibraryItemType.Playlist ? LibraryItemType.Playlist : LibraryItemType.GetParentType(type));
                    parent.Children.Add(item);
                    parent.HasChildren = true;
                }
            }
            return item;
        }
        public LibraryItem GetFixedLibraryItem(string type)
        {
            return LibraryItems.FirstOrDefault(o => o.Id == type);
        }

        /// <exception cref="Exception"><c>Exception</c>.</exception>
        public static void UpdateDatabase()
        {
            var appDataLocation = SystemHelper.GetAppDataLocation();
            AppDomain.CurrentDomain.SetData("DataDirectory", appDataLocation);
            if (!File.Exists(Path.Combine(appDataLocation, "MediaAssistantDb.s3db")))
            {
                ResourceHelper.CopyEmbededResource("MediaAssistant.DAL.MediaAssistantDb.s3db", Path.Combine(appDataLocation, "MediaAssistantDb.s3db"));
            }
            else
            {
                IsUpdatedVersion = IsDatabaseUpdatedVersion();
                if (IsUpdatedVersion == false)
                {
                    RunUpdateScript();
                }
            }
        }

        private static SQLiteConnection GetSqLiteConnection()
        {
            string connectionString = string.Format(@"data source=""{0}\MediaAssistantDb.s3db""",
                                                    SystemHelper.GetAppDataLocation());
            var connection = new SQLiteConnection(connectionString);
            return connection;
        }

        private static bool IsDatabaseUpdatedVersion()
        {
            var connection = GetSqLiteConnection();
            try
            {
                connection.Open();
                var command = new SQLiteCommand(string.Format("Select * from DBUpdate where Version='{0}'",CurrentVersion), connection);
                return command.ExecuteReader().Read();
            }
            catch
            {
                return false;
            }
            finally
            {
                connection.Close();
            }
        }

        private static void RunUpdateScript()
        {
            var connection = GetSqLiteConnection();
            try
            {
                connection.Open();
                var updateScript = GetUpdateScript();
                var commandScripts = updateScript.Split(";".ToCharArray()).Where(s => string.IsNullOrWhiteSpace(s) == false);
                foreach (var commandScript in commandScripts)
                {
                    if(commandScript.Length<5)
                        continue;
                    try
                    {
                        var command = new SQLiteCommand(commandScript, connection);
                        command.ExecuteNonQuery();
                    }
                    catch (Exception)
                    {
                    }
                }
            }
            catch (Exception)
            {

            }
            finally
            {
                connection.Close();
            }

        }

        private static string GetUpdateScript()
        {
            return ResourceHelper.ReadText("MediaAssistant.DAL.DBUpdateScript.UpdateScript.sql");
        }

        public static string GetConnectionString()
        {
            return string.Format(@"Data Source=.\SQLEXPRESS;AttachDbFilename=""{0}"";Integrated Security=True;Connect Timeout=30;User Instance=True", Path.Combine(SystemHelper.GetAppDataLocation(), "MediaAssistantDB.mdf"));
        }

        public ProfileMovie GetOrCreateProfileMovie(Movie movie, Profile selectedProfile)
        {
            if (movie == null)
            {
                return null;
            }
            var profileMovie = selectedProfile.ProfileMovies.FirstOrDefault(pm => pm.MovieId == movie.Id);
            if (profileMovie == null)
            {
                profileMovie = new ProfileMovie
                {
                    Movie = movie,
                    Profile = selectedProfile,
                };
                ProfileMovies.AddObject(profileMovie);
                Save();
            }
            return profileMovie;
        }
        public IEnumerable<Movie> GetNewMovies(Profile selectedProfile)
        {
            return (from movie in Movies.Where(m=>m.FullPath!=null).ToArray()
                    let profileMovie = movie.GetProfileMovie(selectedProfile)
                    where profileMovie == null || profileMovie.IsNewMovie()
                    select movie).ToList();
        }
        public IEnumerable<Movie> GetRecentlyPlayedMovies(Profile profile)
        {
            return (from movie in Movies.Where(m => m.FullPath != null).ToArray()
                    let profileMovie = movie.GetProfileMovie(profile)
                    where profileMovie != null && profileMovie.IsRecentlyPlayedMovie()
                    orderby profileMovie.LastPlayed descending 
                    select movie).ToList();
        }

        public IEnumerable<Movie> GetWatchedMovies(Profile selectedProfile)
        {
            var profileMovieIds = selectedProfile.ProfileMovies.Where(pm => pm.Watched).Select(pm => pm.MovieId).ToList();
            var movies = new List<Movie>();
            foreach (var movie in Movies.Where(m => profileMovieIds.Contains(m.Id)))
            {
                movie.Watched = true;
                movies.Add(movie);
            }
            return movies;
        }
        public IEnumerable<Movie> GetWishListMovie(Profile selectedProfile)
        {
            var profileMovieIds = selectedProfile.ProfileMovies.Where(pm => pm.IsInWishList).Select(pm => pm.MovieId).ToList();
            var movies = new List<Movie>();
            foreach (var movie in Movies.Where(m => profileMovieIds.Contains(m.Id)))
            {
                movie.IsInWishList = true;
                movies.Add(movie);
            }
            return movies;
        }
        public IEnumerable<Movie> GetStarMovies(Profile selectedProfile)
        {
            var profileMovieIds = selectedProfile.ProfileMovies.Where(pm => pm.Star > 0).Select(pm => pm.MovieId).ToList();
            var movies = new List<Movie>();
            foreach (var movie in Movies.Where(m => profileMovieIds.Contains(m.Id)))
            {
                movie.IsStarred = true;
                movies.Add(movie);
            }
            return movies;
        }

        public IEnumerable<Movie> GetFailedMovies()
        {
            return Movies.Where(m => m.Status == MovieStatus.Failed);
        }

        public IEnumerable<string> GetRecommendedMovieTitles(string movieTitle)
        {
            if (string.IsNullOrWhiteSpace(movieTitle))
                return new string[] {};
            try
            {
                var recommendedMovies = RecommendedMovies.Where(m => m.QueryTitle == movieTitle);
                if (recommendedMovies.Any())
                    return recommendedMovies.Select(m => m.RecommendedMovieTitle);

                var content = WebRequestHelper.GetContent(String.Format(URL, movieTitle.Replace(" ", "+")));
                var jsonObject = JObject.Parse(content);
                var jsonArray = jsonObject["Similar"]["Results"];
                var recommendedMovieTitles = jsonArray.Select(token => (string)token.SelectToken("Name")).Where(name => String.IsNullOrWhiteSpace(name) == false).ToList();
                if (recommendedMovieTitles.Count == 0)
                    recommendedMovieTitles.Add(RecommendationNotFound);
                foreach (var recommendedTitle in recommendedMovieTitles)
                {
                    AddRecommendation(movieTitle, recommendedTitle);
                }
                return recommendedMovieTitles;
            }
            catch (Exception)
            {
                return new string[] { };
            }
        }

        private void AddRecommendation(string movieTitle, string recommendedTitle)
        {
            var movieRecommendation = new RecommendedMovie
            {
                Id = Guid.NewGuid().ToString(),
                QueryTitle = movieTitle,
                RecommendedMovieTitle = recommendedTitle,
            };
            RecommendedMovies.AddObject(movieRecommendation);
            Save();
        }

        private const string URL = @"http://www.tastekid.com/ask/ws?q={0}&f=musica1363&k=mjc3njgwogu2&type=movie&format=JSON";
        public const string RecommendationNotFound = "Recommendation not found";
        private const string CurrentVersion = "1.7";

        public void AddMovie(Movie movie)
        {
            Movies.AddObject(movie);
            Save();
        }

        public bool IsIMDb250WishListExists()
        {
            return LibraryItems.Any(o=>o.Type==LibraryItemType.IMDbMustWatchList);
        }

        public List<Movie> GetRecentlyAddedMovies(Profile selectedProfile)
        {
            var lastDate = DateTime.Now.Subtract(TimeSpan.FromDays(30));
            return Movies.Where(m => m.CreateDate > lastDate).ToList();
        }
    }
}
